package com.erp.app.admin.companyMenu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.erp.app.admin.companyMenu.beans.CompanyMenuObjectVO;
import com.erp.app.admin.companyMenu.ejb.CompanyMenuGroupPK;
import com.erp.app.admin.companyMenu.ejb.CompanyMenuObjectPK;
import com.erp.app.common.ProjectConstants;
import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.model.dao.SimplePageEditStyleDao;
import com.erp.framework.model.pk.SimpleBasePk;
import com.erp.framework.model.vo.ChildList;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.util.logger.Logger;

public class CompanyMenuObjectDAO extends SimplePageEditStyleDao {

	protected String sqlPurge(){
        return sqlDelete();
    }

    protected void setValuesForPurge(PreparedStatement pStmt, Object obj) throws SQLException {
        setValuesForDelete(pStmt, obj);
    }

    protected int getTablesInEffect() {
        return 1;
    }

    protected String sqlInsert() {
        return "INSERT INTO T_COMPANY_MENU_OBJECT ("
                + "COMPANY_CODE, GROUP_ID, MENU_ID, ROLE_GROUP_ID, OBJECT_ID, VISIBLE_LEVEL, ENABLE_LEVEL"
                + ", CREATE_USER, CREATE_DATE) " + "VALUES (?,?,?,?,?,?,?,?,sysdate)";
    }

    protected void setValuesForInsert(PreparedStatement pStmt, Object obj) throws SQLException {
        CompanyMenuObjectVO companyMenuObjectVO = (CompanyMenuObjectVO) obj;
        int i = 1;
        pStmt.setString(i++, companyMenuObjectVO.getCompanyCode());
        pStmt.setString(i++, companyMenuObjectVO.getGroupId());
        pStmt.setString(i++, companyMenuObjectVO.getMenuId());
        pStmt.setString(i++, companyMenuObjectVO.getRoleGroupId());
        pStmt.setString(i++, companyMenuObjectVO.getObjectId());
        setNullableBigDecimal(pStmt, i++, companyMenuObjectVO.getVisibleLevel());
        setNullableBigDecimal(pStmt, i++, companyMenuObjectVO.getEnableLevel());
        setNullableString(pStmt, i++, companyMenuObjectVO.getCreateUser());
    }

    protected String sqlUpdate() {
        return "UPDATE T_COMPANY_MENU_OBJECT SET " + "VISIBLE_LEVEL=?, ENABLE_LEVEL=?, UPDATE_USER=?, UPDATE_DATE=sysdate "
                + "WHERE COMPANY_CODE = ? " + "AND GROUP_ID = ? " + "AND MENU_ID = ? " + "AND ROLE_GROUP_ID = ? "
                + "AND OBJECT_ID = ? " + "AND (UPDATE_DATE IS NULL OR UPDATE_DATE = ?) ";
    }

    protected void setValuesForModify(PreparedStatement pStmt, Object obj) throws SQLException {
        CompanyMenuObjectVO companyMenuObjectVO = (CompanyMenuObjectVO) obj;
        int i = 1;

//         System.out.println("****getVisibleLevel=["+companyMenuObjectVO.getVisibleLevel()+"]");
//         System.out.println("****getEnableLevel=["+companyMenuObjectVO.getEnableLevel()+"]");
//         System.out.println("****getUpdateUser=["+companyMenuObjectVO.getUpdateUser()+"]");
//         System.out.println("****getCompanyCode=["+companyMenuObjectVO.getCompanyCode()+"]");
//         System.out.println("****getGroupId=["+companyMenuObjectVO.getGroupId()+"]");
//         System.out.println("****getMenuId=["+companyMenuObjectVO.getMenuId()+"]");
//         System.out.println("****getRoleGroupId=["+companyMenuObjectVO.getRoleGroupId()+"]");
//         System.out.println("****getObjectId=["+companyMenuObjectVO.getObjectId()+"]");
//         System.out.println("****getUpdateDate=["+companyMenuObjectVO.getUpdateDate()+"]");

        setNullableBigDecimal(pStmt, i++, companyMenuObjectVO.getVisibleLevel());
        setNullableBigDecimal(pStmt, i++, companyMenuObjectVO.getEnableLevel());
        setNullableString(pStmt, i++, companyMenuObjectVO.getUpdateUser());
        pStmt.setString(i++, companyMenuObjectVO.getCompanyCode());
        pStmt.setString(i++, companyMenuObjectVO.getGroupId());
        pStmt.setString(i++, companyMenuObjectVO.getMenuId());
        pStmt.setString(i++, companyMenuObjectVO.getRoleGroupId());
        pStmt.setString(i++, companyMenuObjectVO.getObjectId());
        setNullableTimestamp(pStmt, i++, companyMenuObjectVO.getUpdateDate());
    }

    protected String sqlDelete() {
        return "DELETE FROM T_COMPANY_MENU_OBJECT" + " WHERE COMPANY_CODE = ? " + " AND GROUP_ID = ? " + " AND MENU_ID = ? "
                + " AND ROLE_GROUP_ID = ? " + " AND OBJECT_ID = ? ";
    }

    protected void setValuesForDelete(PreparedStatement pStmt, Object obj) throws SQLException {
        CompanyMenuObjectVO companyMenuObjectVO = (CompanyMenuObjectVO) obj;
        int i = 1;
        pStmt.setString(i++, companyMenuObjectVO.getCompanyCode());
        pStmt.setString(i++, companyMenuObjectVO.getGroupId());
        pStmt.setString(i++, companyMenuObjectVO.getMenuId());
        pStmt.setString(i++, companyMenuObjectVO.getRoleGroupId());
        pStmt.setString(i++, companyMenuObjectVO.getObjectId());
    }

    protected String sqlFind() {
        return "SELECT COMPANY_CODE, GROUP_ID, MENU_ID, ROLE_GROUP_ID, OBJECT_ID, VISIBLE_LEVEL, ENABLE_LEVEL"
                + ", CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE " + "FROM T_COMPANY_MENU_OBJECT "
                + "WHERE COMPANY_CODE = ? " + "AND GROUP_ID = ? " + "AND MENU_ID = ? " + "AND ROLE_GROUP_ID = ? "
                + "AND OBJECT_ID = ? ";
    }

    protected void setValuesForFind(PreparedStatement pStmt, Object obj) throws SQLException {
        CompanyMenuObjectPK pk = (CompanyMenuObjectPK) obj;
        int i = 1;
        pStmt.setString(i++, pk.getCompanyCode());
        pStmt.setString(i++, pk.getGroupId());
        pStmt.setString(i++, pk.getMenuId());
        pStmt.setString(i++, pk.getRoleGroupId());
        pStmt.setString(i++, pk.getObjectId());
    }

    protected String sqlList() {
        return "SELECT COMPANY_CODE, GROUP_ID, MENU_ID, ROLE_GROUP_ID, OBJECT_ID, VISIBLE_LEVEL, ENABLE_LEVEL"
                + ", CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE " + "FROM T_COMPANY_MENU_OBJECT "
                + "WHERE COMPANY_CODE = ? " + "AND GROUP_ID = ? " + "AND MENU_ID = ? " + "AND ROLE_GROUP_ID = ? "
                + " ORDER BY COMPANY_CODE, GROUP_ID, MENU_ID, ROLE_GROUP_ID, OBJECT_ID ";

        // String sql = "SELECT DECODE(B.COMPANY_CODE, NULL, B.COMPANY_CODE,
        // A.COMPANY_CODE) COMPANY_CODE,"
        // + " DECODE(B.GROUP_ID, NULL, B.GROUP_ID, A.GROUP_ID) GROUP_ID,"
        // + " DECODE(B.MENU_ID, NULL, B.MENU_ID, A.MENU_ID) MENU_ID,"
        // + " A.ROLE_GROUP_ID ROLE_GROUP_ID, "
        // + " B.OBJECT_ID OBJECT_ID, "
        // + " A.VISIBLE_LEVEL VISIBLE_LEVEL,"
        // + " A.ENABLE_LEVEL ENABLE_LEVEL,"
        // + " A.CREATE_USER CREATER_USER, "
        // + " A.CREATE_DATE CREATE_DATE, "
        // + " A.UPDATE_USER UPDATE_USER, "
        // + " A.UPDATE_DATE DATE_DATE,"
        // + " DECODE(A.COMPANY_CODE,NULL,0,1) EXIST_IND"
        // + " FROM T_COMPANY_MENU_OBJECT A, T_MENU_OBJECT B"
        // + " WHERE B.COMPANY_CODE = ? "
        // + " AND B.GROUP_ID = ?"
        // + " AND B.MENU_ID = ?"
        // + " AND A.ROLE_GROUP_ID(+) = ?"
        // + " AND B.COMPANY_CODE = A.COMPANY_CODE(+)"
        // + " AND B.GROUP_ID=A.GROUP_ID(+)"
        // + " AND B.MENU_ID=A.MENU_ID(+)"
        // + " AND B.OBJECT_ID=A.OBJECT_ID(+)";
        // System.out.println("CompanyMenuObjectDAO, sql=" + sql);
        // return sql;
    }

    protected void setValuesForList(PreparedStatement pStmt, Object obj) throws SQLException {
        CompanyMenuGroupPK pk = (CompanyMenuGroupPK) obj;
        int i = 1;
        pStmt.setString(i++, pk.getCompanyCode());
        pStmt.setString(i++, pk.getGroupId());
        pStmt.setString(i++, pk.getMenuId());
        pStmt.setString(i++, pk.getRoleGroupId());
    }

    protected SimpleBaseVo getResultSetValues(ResultSet rs) throws SQLException {
        CompanyMenuObjectVO companyMenuObjectVO = new CompanyMenuObjectVO();
        companyMenuObjectVO.setCompanyCode(rs.getString("COMPANY_CODE"));
        companyMenuObjectVO.setGroupId(rs.getString("GROUP_ID"));
        companyMenuObjectVO.setMenuId(rs.getString("MENU_ID"));
        companyMenuObjectVO.setRoleGroupId(rs.getString("ROLE_GROUP_ID"));
        companyMenuObjectVO.setObjectId(rs.getString("OBJECT_ID"));
        companyMenuObjectVO.setVisibleLevel(rs.getBigDecimal("VISIBLE_LEVEL"));
        companyMenuObjectVO.setEnableLevel(rs.getBigDecimal("ENABLE_LEVEL"));
        companyMenuObjectVO.setCreateUser(rs.getString("CREATE_USER"));
        companyMenuObjectVO.setCreateDate(rs.getTimestamp("CREATE_DATE"));
        companyMenuObjectVO.setUpdateUser(rs.getString("UPDATE_USER"));
        companyMenuObjectVO.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));
        // companyMenuObjectVO.setExistInd(rs.getString("EXIST_IND"));
        return companyMenuObjectVO;
    }

    public final ChildList selectByParent(SimpleBaseVo parent, Connection conn) throws FindRecordException {
        // dummy method
        return selectByParent(parent, CompanyMenuObjectVO.class, conn);
    }

    public final ChildList selectByParent(SimpleBaseVo parent, Class childType, Connection conn) throws FindRecordException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        ChildList rslt = new ChildList(childType);

        try {
            for (short i = 0, j; i < getTablesInEffect(); i++) {

                pStmt = conn.prepareStatement(sqlList(i));
                setValuesForList(pStmt, parent.getPrimaryKey(), i);

                j = 0;
                rs = pStmt.executeQuery();

                while (rs.next()) {
                    if (i == 0) {
                        SimpleBaseVo childVO = (SimpleBaseVo) getResultSetValues(rs, null, i);
                        childVO.setParent(parent);
                        childVO.setIsExisting(true);
                        childVO.setIsDirty(false);

                        rslt.add(childVO);
                    } else {
                        SimpleBaseVo childVO = (SimpleBaseVo) rslt.get(j);
                        if (childVO != null) {
                            childVO = (SimpleBaseVo) getResultSetValues(rs, childVO, i);
                            rslt.set(j, childVO);
                        }
                        j++;
                    }
                }
            }
        } catch (SQLException sqe) {
            System.err.println("CompanyMenuObjectDAO.selectByParent(): " + (sqe.getMessage()));
            throw new FindRecordException(sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException sqe) {
                System.err.println("CompanyMenuObjectDAO.selectByParent() [" + childType.getName() + "] closing rs: "
                        + sqe.getMessage());
            }

            try {
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
                System.err.println("CompanyMenuObjectDAO.selectByParent() [" + childType.getName() + "] closing stmt: "
                        + sqe.getMessage());
            }
        }
        return rslt;
    }

    protected String sqlList(short i) {
        if (getTablesInEffect() == 1) {
            return sqlList();
        } else {
            throw new UnsupportedOperationException("CompanyMenuObjectDAO.sqlList was not implemented.");
        }
    }

    protected void setValuesForList(PreparedStatement pStmt, Object obj, short i) throws SQLException {
        if (getTablesInEffect() == 1) {
            setValuesForList(pStmt, obj);
        } else {
            throw new UnsupportedOperationException("CompanyMenuObjectDAO.setValuesForList was not implemented.");
        }
    }

    protected SimpleBaseVo getResultSetValues(ResultSet rs, SimpleBaseVo model, short i) throws SQLException {
        if (getTablesInEffect() == 1) {
            return getResultSetValues(rs);
        } else {
            throw new UnsupportedOperationException("CompanyMenuObjectDAO.getResultSetValues was not implemented.");
        }
    }

    public final SimpleBaseVo findByPrimaryKey(Object obj, Connection conn) throws FindRecordException {
        return findByPrimaryKey((SimpleBasePk) obj, conn);
    }

    public final SimpleBaseVo findByPrimaryKey(SimpleBasePk pk, Connection conn) throws FindRecordException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        SimpleBaseVo model = null;

        try {
            for (short i = 0; i < getTablesInEffect(); i++) {

                pStmt = conn.prepareStatement(sqlFind(i));
                setValuesForFind(pStmt, pk, i);

                rs = pStmt.executeQuery();

                if (rs.next()) {
                    if (i == 0) {
                        model = (SimpleBaseVo) getResultSetValues(rs, null, i);
                    } else {
                        model = (SimpleBaseVo) getResultSetValues(rs, model, i);
                    }
                }
            }
        } catch (SQLException sqe) {
        	Logger.logExceptions(this, sqe);
            throw new FindRecordException(sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException sqe) {
                System.err.println("CompanyMenuObjectDAO.findByPrimaryKey() [" + pk.getClass().getName() + "] closing rs: "
                        + sqe.getMessage());
            }

            try {
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
                System.err.println("CompanyMenuObjectDAO.findByPrimaryKey() [" + pk.getClass().getName() + "] closing stmt: "
                        + sqe.getMessage());
            }
        }
        return model;
    }

    protected String sqlFind(short i) {
        if (getTablesInEffect() == 1) {
            return sqlFind();
        } else {
            throw new UnsupportedOperationException("CompanyMenuObjectDAO.sqlFind was not implemented.");
        }
    }

    protected void setValuesForFind(PreparedStatement pStmt, Object obj, short i) throws SQLException {
        if (getTablesInEffect() == 1) {
            setValuesForFind(pStmt, obj);
        } else {
            throw new UnsupportedOperationException("CompanyMenuObjectDAO.setValuesForFind was not implemented.");
        }
    }

}
